/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package isd.inventario.dao;

import isd.general.dao.BaseDAO;
import java.util.Map;
import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.Query;


/**
 *
 * @author ctrosch
 */
@Stateless
@LocalBean
public class ST_ListaPrecioDAO extends BaseDAO{    

    /**
    public List<ItemCatalogo> getItemCatalogo(int first, int max, String sortField, SortOrder so , Map<String,String> filtro){

        try {

            String sQuery = "SELECT i FROM ItemCatalogo i ";
            sQuery += generarStringFiltro(filtro,"i", true);
            sQuery += " ORDER BY i.nrocta, i.tippro, i.artcod ASC";

//            System.err.println(sQuery);
            Query q = em.createQuery(sQuery);

            if(max>=1) {
                q.setMaxResults(max);
            }
            
            return q.getResultList();

        } catch (Exception e) {

            e.printStackTrace();
            System.err.println("Error al consultar precios");
            return new ArrayList<ItemCatalogo>();
        }
    }
     * */
    
    public Integer getCantidadItemCatalogo(Map<String, String> filtro){


        try {
            String sQuery = "SELECT count(i) FROM ItemCatalogo i ";
            sQuery += generarStringFiltro(filtro,"i", true);

            return queryObject(Long.class, sQuery).intValue();

        } catch (Exception e) {
            System.out.println("Error al consultar precios");
            return null;
        }
    }

    /**
    public ItemCatalogo getItemCatalogoByProducto(Map<String, String> filtro){

        try {
            String sQuery = "SELECT i FROM ItemCatalogo i " 
                    + generarStringFiltro(filtro, "i", true);
           
            Query q = em.createQuery(sQuery);           
            //System.out.println(sQuery);
            return (ItemCatalogo) q.getSingleResult();

        } catch (Exception e) {
            System.out.println("Error al consultar item ");
            return null;
        }
    }
     * */

    public void generarCatalogoCliente(String nroCta) {
        try {
            String sQuery = "EXEC GENERAR_CATALOGO_CLIENTE '"+nroCta+"'";

            Query q = em.createNativeQuery(sQuery);

            int r = q.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Error al generar catálogo para "+ nroCta);
            
            
        }
    }

    public void borrarCatalogoCliente(String nroCta){
        try {
            String sQuery = "DELETE FROM ITEM_CATALOGO WHERE ITEM_CATALOGO.NROCTA = '"+nroCta+"'";

            Query q = em.createNativeQuery(sQuery);

            int r = q.executeUpdate();

        } catch (Exception e) {
            System.out.println("Error borrar catálogo ");
            e.printStackTrace();

        }
    }

    public void limpiarCatalogo(){
        try {
            String sQuery = "DELETE FROM ITEM_CATALOGO WHERE ITEM_CATALOGO.NROCTA <> '999999'";

            Query q = em.createNativeQuery(sQuery);

            int r = q.executeUpdate();

        } catch (Exception e) {
            System.out.println("Error al generar catálogo ");
            e.printStackTrace();
        }
    }

    



    /**
    public List<Object[]> getPrecios(int first, int max, String sortField, SortOrder so , Map<String,String> filtro){

        try {
            String sQuery = "SELECT "
                    + "PRO.STMPDH_TIPPRO,"
                    + "PRO.STMPDH_ARTCOD,"
                    + "PRO.STMPDH_DESCRP,"
                    + "	   (ISNULL((SELECT USR_EXCPRD_PRECIO FROM USR_EXCPRD WHERE USR_EXCPRD_NROCTA =  CLI.VTMCLH_NROCTA AND USR_EXCPRD_TIPPRO = PRO.STMPDH_TIPPRO "
                    + "				AND USR_EXCPRD_ARTCOD = PRO.STMPDH_ARTCOD AND USR_EXCPRD_PRECIO >0), "
                    + "	          (SELECT TOP 1 P1.STTPRE_PRECIO FROM STTPRE P1 WHERE P1.STTPRE_CODLIS = PRE.STTPRE_CODLIS  AND P1.STTPRE_TIPPRO = PRE.STTPRE_TIPPRO "
                    + "			  AND P1.STTPRE_ARTCOD = STTPRE_ARTCOD AND P1.STTPRE_FECLIS <= {d '2012-08-17'} order by STTPRE_FECLIS DESC)) "
                    + "	   )AS STTPRE_PRECIO,   	   	    "
                    + "	   (ISNULL(PRO.STMPDH_MONPRD,'ARS')) ,	    "
                    + "	   (ISNULL((SELECT USR_EXCPRD_PORCEN FROM USR_EXCPRD WHERE USR_EXCPRD_NROCTA =  CLI.VTMCLH_NROCTA AND USR_EXCPRD_TIPPRO = PRO.STMPDH_TIPPRO AND USR_EXCPRD_ARTCOD = PRO.STMPDH_ARTCOD ), "
                    + "	    ISNULL((SELECT USR_EXCCLI_PORCEN FROM USR_EXCCLI WHERE  USR_EXCCLI_NROCTA =  CLI.VTMCLH_NROCTA AND USR_EXCCLI_TIPPRO = PRO.STMPDH_TIPPRO AND USR_EXCCLI_TIPPR1 = PRO.STMPDH_TIPPRO "
                    + "	            AND USR_EXCCLI_TIPPR2 = PRO.STMPDH_TIPPRO AND USR_EXCCLI_PRFIX1 = 'ARTCOD' AND USR_EXCCLI_PRFIX2 = 'ARTCOD' AND USR_EXCCLI_NRORU1 = 1  AND USR_EXCCLI_NRORU2 = 2  "
                    + "	            AND USR_EXCCLI_VALRU1 = PRO.STMPDH_RUBR01 AND USR_EXCCLI_VALRU2 = PRO.STMPDH_RUBR02),0))) AS BONIFICACION,	   "
                    + "	   (ISNULL((SELECT (SUM(STRMVK_STOCKS)) FROM STRMVK WHERE STRMVK_DEPOSI = '20' AND STRMVK_SECTOR = '0' AND STRMVK_TIPPRO = PRO.STMPDH_TIPPRO AND STRMVK_ARTCOD = PRO.STMPDH_ARTCOD "
                    + "				GROUP BY STRMVK.STRMVK_DEPOSI, STRMVK.STRMVK_SECTOR),0)) AS NOA,	   	   "
                    + "	   (ISNULL((SELECT (SUM(STRMVK_STOCKS)) FROM STRMVK WHERE STRMVK_DEPOSI = '40' AND STRMVK_SECTOR = '0' AND STRMVK_TIPPRO = PRO.STMPDH_TIPPRO AND STRMVK_ARTCOD = PRO.STMPDH_ARTCOD "
                    + "				GROUP BY STRMVK.STRMVK_DEPOSI, STRMVK.STRMVK_SECTOR),0)) AS NEA,	   	   	   	   "
                    + "	   (ISNULL((SELECT (SUM(STRMVK_STOCKS)) FROM STRMVK WHERE STRMVK_DEPOSI = '40' AND STRMVK_SECTOR = '0' AND STRMVK_TIPPRO = PRO.STMPDH_TIPPRO AND STRMVK_ARTCOD = PRO.STMPDH_ARTCOD "
                    + "				GROUP BY STRMVK.STRMVK_DEPOSI, STRMVK.STRMVK_SECTOR),0)) AS BUE,	   	   "
                    + "	   (ISNULL((SELECT (SUM(STRMVK_STOCKS)) FROM STRMVK WHERE STRMVK_DEPOSI = '50' AND STRMVK_SECTOR = '0' AND STRMVK_TIPPRO = PRO.STMPDH_TIPPRO AND STRMVK_ARTCOD = PRO.STMPDH_ARTCOD "
                    + "				GROUP BY STRMVK.STRMVK_DEPOSI, STRMVK.STRMVK_SECTOR),0)) AS CUY, "
                    + "	PRO.USR_STMPDH_IMGCHI, "
                    + " PRO.USR_STMPDH_IMGGRA,"
                    + " PRO.USR_STMPDH_PNUEVO, "
                    + " PRO.USR_STMPDH_IMPORT, "
                    + " PRO.STMPDH_STKMIN "
                    + "FROM TEM.dbo.STTLPR LIS, TEM.dbo.STMPDH PRO, TEM.dbo.STTPRE PRE , TEM.dbo.VTMCLH CLI "
                    + "WHERE (PRE.STTPRE_DEBAJA = 'N') "
                    + "  AND (PRO.USR_STMPDH_INTNET = 'S')"
                    + "  AND (PRO.STMPDH_DEBAJA = 'N') "
                    + "  AND (LIS.STTLPR_CODLIS = PRE.STTPRE_CODLIS) "
                    + "  AND (PRE.STTPRE_CODLIS = CLI.VTMCLH_CNDPRE)   "
                    + "  AND (PRO.STMPDH_ARTCOD = PRE.STTPRE_ARTCOD) "
                    + "  AND (PRO.STMPDH_TIPPRO = PRE.STTPRE_TIPPRO)"
                    + "  AND ('S' NOT IN (SELECT USR_EXCPRD_NOFACT FROM USR_EXCPRD WHERE USR_EXCPRD_NROCTA = CLI.VTMCLH_NROCTA AND USR_EXCPRD_TIPPRO = PRO.STMPDH_TIPPRO AND USR_EXCPRD_ARTCOD = PRO.STMPDH_ARTCOD) )"
                    + "  AND ('S' NOT IN (SELECT USR_EXCCLI_NOFACT FROM USR_EXCCLI WHERE USR_EXCCLI_NROCTA =  CLI.VTMCLH_NROCTA AND USR_EXCCLI_TIPPRO = PRO.STMPDH_TIPPRO AND USR_EXCCLI_TIPPR1 = PRO.STMPDH_TIPPRO"
                    + "  				AND USR_EXCCLI_TIPPR2 = PRO.STMPDH_TIPPRO AND USR_EXCCLI_PRFIX1 = 'ARTCOD'  AND USR_EXCCLI_PRFIX2 = 'ARTCOD'  AND USR_EXCCLI_NRORU1 = 1  AND USR_EXCCLI_NRORU2 = 2  "
                    + "  				AND USR_EXCCLI_VALRU1 = PRO.STMPDH_RUBR01 AND USR_EXCCLI_VALRU2 = PRO.STMPDH_RUBR02))";



            //Si el filtro no está vacio lo aplicamos
            if (!filtro.isEmpty()) {

                Iterator it = filtro.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry ent = (Map.Entry) it.next();
                    sQuery = sQuery + " AND (" + ent.getKey() + ent.getValue() + ") ";
                }
            }

            List<Object[]> resultado = em.createNativeQuery(sQuery).getResultList();

            return resultado;

        } catch (Exception e) {
            System.out.println("Error al consultar precios");
            return null;
        }
    }
    */

    /**
    public Integer getCantidadPrecioProducto(Map<String, String> filtro) {

        try {
            String sQuery = "SELECT COUNT(*) FROM TEM.dbo.STTLPR LIS, TEM.dbo.STMPDH PRO, TEM.dbo.STTPRE PRE , TEM.dbo.VTMCLH CLI "
            + "WHERE (PRE.STTPRE_DEBAJA = 'N') "
            + "  AND (PRO.USR_STMPDH_INTNET = 'S')"
            + "  AND (PRO.STMPDH_DEBAJA = 'N') "
            + "  AND (LIS.STTLPR_CODLIS = PRE.STTPRE_CODLIS) "
            + "  AND (PRE.STTPRE_CODLIS = CLI.VTMCLH_CNDPRE)   "
            + "  AND (PRO.STMPDH_ARTCOD = PRE.STTPRE_ARTCOD) "
            + "  AND (PRO.STMPDH_TIPPRO = PRE.STTPRE_TIPPRO)"
            + "  AND ('S' NOT IN (SELECT USR_EXCPRD_NOFACT FROM USR_EXCPRD WHERE USR_EXCPRD_NROCTA = CLI.VTMCLH_NROCTA AND USR_EXCPRD_TIPPRO = PRO.STMPDH_TIPPRO AND USR_EXCPRD_ARTCOD = PRO.STMPDH_ARTCOD) )"
            + "  AND ('S' NOT IN (SELECT USR_EXCCLI_NOFACT FROM USR_EXCCLI WHERE USR_EXCCLI_NROCTA =  CLI.VTMCLH_NROCTA AND USR_EXCCLI_TIPPRO = PRO.STMPDH_TIPPRO AND USR_EXCCLI_TIPPR1 = PRO.STMPDH_TIPPRO"
            + "  				AND USR_EXCCLI_TIPPR2 = PRO.STMPDH_TIPPRO AND USR_EXCCLI_PRFIX1 = 'ARTCOD'  AND USR_EXCCLI_PRFIX2 = 'ARTCOD'  AND USR_EXCCLI_NRORU1 = 1  AND USR_EXCCLI_NRORU2 = 2  "
            + "  				AND USR_EXCCLI_VALRU1 = PRO.STMPDH_RUBR01 AND USR_EXCCLI_VALRU2 = PRO.STMPDH_RUBR02))";

            //Si el filtro no está vacio lo aplicamos
            if (!filtro.isEmpty()) {

                Iterator it = filtro.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry ent = (Map.Entry) it.next();
                    sQuery = sQuery + " AND (" + ent.getKey() + ent.getValue() + ") ";
                }
            }
            Query q = em.createNativeQuery(sQuery);
            System.out.print("Resultado cantidad: " +  q.getSingleResult());
            return ((Integer) q.getSingleResult());

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("Error al obtener cantidad de registros disponibles");
            return 0;
        }
    }
    */

}
